import hashlib
import os
import sqlite3
from datetime import datetime

import dateutil.relativedelta
import pandas as pd
from openpyxl import styles
from openpyxl.styles import Border, Side

from RwConfig import *
from fun_list import *


class checkdata:
    def __init__(self):
        self.funlist = fun_list()
        self.get_config()

        # 连接（自动创建数据库文件）
        self.db_name = 'D:\\DataBase\\ini\\MarCheckDB.db'
        self.conn_flag = 0  # 连接标志
        self.count = False  # 目录路径 默认当前路径
        self.directory_path = os.getcwd()  # 目录路径 默认当前路径
        self.export_dir = self.directory_path + "\导出结果"  # 导出结果存放文件夹
        # print(self.export_dir)
        if not os.path.exists(self.export_dir):
            os.makedirs(self.export_dir)

        # 检查数据库文件是否存在
        if not os.path.exists("D:\\DataBase\\ini"):
            # print(f"数据库文件 {db_name} 不存在，无法连接。")
            os.makedirs("D:\\DataBase\\ini")

    def get_config(self):
        configname = 'config/config.ini'
        # 初始化配置文件
        self.RwConfig = RwConfig(configname)
        # 获取配置文件信息
        self.citycode = self.RwConfig.Get_Config('info', 'citycode')
        self.cityname = self.RwConfig.Get_Config('info', 'cityname')
        # name = self.RwConfig.Get_Config('connect', 'username')
        self.pa = self.RwConfig.Get_Config('connect', 'password')
        self.check_name = self.RwConfig.Get_Config_Boolean('checklist', 'name')
        self.check_sfz = self.RwConfig.Get_Config_Boolean('checklist', 'sfz')
        self.check_ratio = self.RwConfig.Get_Config_Boolean('checklist', 'ratio')
        self.ratiodw_min = int(self.RwConfig.Get_Config('checklist', 'dw').split('-')[0])
        self.ratiodw_max = int(self.RwConfig.Get_Config('checklist', 'dw').split('-')[1])
        self.ratiogr_min = int(self.RwConfig.Get_Config('checklist', 'gr').split('-')[0])
        self.ratiogr_max = int(self.RwConfig.Get_Config('checklist', 'gr').split('-')[1])
        self.check_base = self.RwConfig.Get_Config_Boolean('checklist', 'base')
        self.base_min = int(self.RwConfig.Get_Config('checklist', 'bnum').split('-')[0])
        self.base_max = int(self.RwConfig.Get_Config('checklist', 'bnum').split('-')[1])
        self.check_buser = self.RwConfig.Get_Config_Boolean('checklist', 'bankuser')
        self.check_blcy = self.RwConfig.Get_Config_Boolean('checklist', 'blcy')
        # print(self.check_name, self.check_sfz, self.check_ratio, self.check_ratio, self.cityname,
        #       self.pa)
        # print('check_ratiodw:', self.check_ratio, self.ratiodw_min, self.ratiodw_max)
        # print('check_ratiogr:', self.check_ratio, self.ratiogr_min, self.ratiogr_max)
        # print('check_base:', self.check_base, self.base_min, self.base_max)
        print(f"{self.cityname} {self.citycode} 校验开始………………………………")

    def md5_handle(self, pwd):
        """加密逆向还原"""
        str_pwd = pwd
        m5 = hashlib.md5()
        m5.update(str_pwd.encode('utf-8'))
        return m5.hexdigest()

    # df = pd.read_sql("SELECT * FROM v_征缴计划月明细", conn)
    # 将DataFrame写入SQLite数据库
    # df.to_sql('原始数据', conn, if_exists='replace', index=False)

    # '姓名'	'公民身份号码'	'缴费起始年月'	'缴费截止年月'	'对应时段缴费工资基数'	'对应单位缴费比例(%)'	'对应个人缴费比例(%)'	'个人账户清算类型'	'户名'	'银行账号'	'开户行名称'
    # conn = sqlite3.connect(self.db_name)
    # 定义日期格式验证函数
    def check_date(self, filename):
        try:
            db_name = 'D:\\DataBase\\ini\\MarCheckDB.db'
            conn = sqlite3.connect(db_name)
            # 获取游标，用于执行SQL语句
            cursor = conn.cursor()
            # conn.execute("SELECT * FROM ny")
            conn.execute("delete FROM 原始数据")
            # print(f" {name} {citycode}")
            # print(f"SELECT * FROM users WHERE name='{name}' and ss='{pa}'")
            user = pd.read_sql(
                f"SELECT * FROM users WHERE name='{self.cityname}' and ss='{self.md5_handle(self.cityname + self.pa + 'pw')}'",
                conn)
            # print(f"user:{user}")
            # print(self.md5_handle(self.cityname + self.pa+'pw'), self.cityname + self.pa+'pw')
            if user.shape[0] == 1:
                conn_flag = 0
                print(f"初始化成功！")
            else:
                conn_flag = -1

        except sqlite3.OperationalError as e:
            # 捕获异常并打印错误信息
            conn_flag = -1
            print(f"初始化失败 错误：no such files:check ")
            print(f"部分功能不能使用，将继续执行数据校验……")
        # finally:
        #     conn.close

        filettype = filename.split(".")[-1]  # 原始接收文件类型
        export_filename = filename[0:len(filename) - len(filettype) - 1] + "-征缴信息" + '.' + filettype  # 将导出的征缴计划文件
        errs_filename = filename[0:len(filename) - len(filettype) - 1] + "-异常信息" + '.' + filettype  # 将导出的校验信息文件
        if not os.path.exists(filename):
            print(f"当前路径下没有找到文件【{filename}】")
        filepath = os.path.join(self.directory_path, filename)
        # print(filepath)
        # 读取原始数据
        data_cj = pd.read_excel(filepath, header=0, usecols="A:K",
                                names=['姓名', '公民身份号码', '起始年月', '截止年月', '基数', '单位比例', '个人比例',
                                       '清算类型', '户名', '银行账号', '开户行名称'])
        # 数字int类型转换为字符串字符类型，避免调用出现不必要的异常
        data_cj['公民身份号码'] = data_cj['公民身份号码'].astype(str)
        data_cj['起始年月'] = data_cj['起始年月'].astype(str)
        data_cj['截止年月'] = data_cj['截止年月'].astype(str)
        data_cj['银行账号'] = data_cj['银行账号'].astype(str)
        # print(f"{data_cj.head()}")
        # print(data_cj.index)  # 打印索引值

        # 增加新列
        data_check = data_cj.assign(异常信息='',  # 空列
                                    序号=lambda x: range(1, len(data_cj) + 1)  # 自动生成顺序号,可以作为主键
                                    )

        # print(data_check)
        rownum = len(data_check)
        print(f"共{rownum}条记录")
        # 查找 征缴对应 开始年月 截止年月
        begin_month = data_check['起始年月'].min()
        end_month = data_check['截止年月'].max()
        if not self.funlist.is_valid_date(begin_month):
            return f"起始年月【{begin_month}】不符合格式[YYYYMM]", ''
        if not self.funlist.is_valid_date(end_month):
            return f"截止年月【{end_month}】不符合格式[YYYYMM]", ''

        isnumber_err = 0  # 定义非数字标志
        # 循环校验数据
        for i, row in data_check.iterrows():
            # gr = data_check[data_check['异常信息'] != '']
            err_text = ''
            # row = data_check.iloc[i]
            # print(f"i={i}::{row}")
            序号 = row.get('序号')
            姓名 = row.get('姓名')
            公民身份号码 = row.get('公民身份号码')
            起始年月 = row.get('起始年月')
            截止年月 = row.截止年月
            基数 = row.基数
            单位比例 = row.单位比例
            个人比例 = row.个人比例
            清算类型 = row.清算类型
            户名 = row.户名
            银行账号 = row.银行账号
            开户行名称 = row.开户行名称
            if not self.funlist.is_valid_date(起始年月):
                err_text = f"缴费起始年月【{起始年月}】不符合格式要求;"
            if not self.funlist.is_valid_date(截止年月):
                err_text = err_text + f"缴费截止年月【{截止年月}】不符合格式要求;"
            # 校验身份证
            if self.check_sfz:
                if not self.funlist.is_valid_id(公民身份号码):
                    err_text = err_text + f"[{公民身份号码}]身份证号码不正确;"

            gr = data_check[(data_check['公民身份号码'] == 公民身份号码) & (data_check['序号'] != 序号)]
            # gr = gr.sort_values(by=['公民身份号码', '起始年月'])
            # print(f"{姓名} 公民身份号码={公民身份号码}:起始年月={起始年月}\n{gr.loc[:,'姓名':'截止年月']}")
            err1, err2 = '', ''
            for j in range(0, len(gr)):
                # 个人信息 存放grrows 与当前个人信息比较
                grrows = gr.iloc[j]
                # print()
                try:
                    if int(grrows.起始年月) <= int(起始年月) <= int(grrows.截止年月):
                        err1 = err1 + f"[{起始年月}]与序号{grrows.序号}时间段{grrows.起始年月}-{grrows.截止年月}有重复;"
                except Exception as e:
                    pass
                    # print(起始年月)
                    # err1 = err1 + f"校验异常，请排查此人年月是否正常;"
                if self.check_name:
                    if grrows.姓名 != 姓名:
                        err2 = err2 + f"{公民身份号码}姓名不一致，如【序号{grrows.序号}-姓名【{grrows.姓名}】;"
            err_text = err_text + err1 + err2
            if not (self.funlist.is_number(基数) and self.funlist.is_number(单位比例) and self.funlist.is_number(
                    个人比例)):
                isnumber_err = -1  # 缴费基数、比例不准确则不计算明细
                err_text = err_text + f"序号{grrows.序号} 缴费基数、比例存在非数字字符;"
            else:
                # 基数 比例 存在非数字情况下此处不执行
                if self.check_base:  # 判断是否校验比例
                    if 基数 < self.base_min or 基数 > self.base_max:
                        err_text = err_text + f"缴费基数应在[{self.base_min}~{self.base_max}]之间;"
                if self.check_ratio:  # 判断是否校验比例
                    if 单位比例 < self.ratiodw_min or 单位比例 > self.ratiodw_max:
                        err_text = err_text + f"单位比例%应在[{self.ratiodw_min}~{self.ratiodw_max}]之间;"
                    if 个人比例 < self.ratiogr_min or 个人比例 > self.ratiogr_max:
                        err_text = err_text + f"个人比例%应[{self.ratiogr_min}~{self.ratiogr_max}]之间;"
            if 姓名 != 户名 and self.check_buser:
                bankuser = '银行户名与姓名不一致'
            if len(err_text) > 0:
                data_check.at[i, '异常信息'] = err_text

        # 循环校验结束处理内容
        if isnumber_err == 0:  # 存在数据g无法计算征缴计划
            # 处待校验完成后添加直接计算并新增列
            data_check['单位缴费'] = round(data_check['基数'] * data_check['单位比例'] / 100, 2)
            data_check['个人缴费'] = round(data_check['基数'] * data_check['个人比例'] / 100, 2)
            data_check['总金额'] = round((data_check['单位缴费'] + data_check['个人缴费']), 2)

            zjjh = []  # 征缴计划表数据存放    征缴计划年月	征缴计划金额	实际缴费金额	缴费到账时间
            print(f"征缴开始年月:{begin_month}  征缴截止年月:{end_month} ")
            # 生成征缴计划
            zjjh_month = begin_month
            while zjjh_month <= end_month:
                # tdate = datetime.strptime(begin_month, "%Y%m")
                # print(f'=={begin_month}---{tdate}=======')
                # tdate = datetime.strptime(begin_month, "%Y%m") + dateutil.relativedelta.relativedelta(months=1)
                # 根据征缴年月，循环取每个月单位下所有人员缴费金额合计
                zj_month = data_check[(data_check['起始年月'] <= zjjh_month) & (data_check['截止年月'] >= zjjh_month)]
                dwje = round(zj_month['单位缴费'].sum(), 2)
                grje = round(zj_month['个人缴费'].sum(), 2)
                zjje = round(zj_month['总金额'].sum(), 2)
                zj_t = [int(zjjh_month), zjje, '', '', dwje, grje]  # 缴费金额合计等信息 组成元组
                zjjh.append(zj_t)  # 每个月征缴信息添加到zjjh列表
                # print(f'=={begin_month}---{zjje}==={dwje}=+={grje}==')
                zjjh_month = (datetime.strptime(zjjh_month, "%Y%m") + dateutil.relativedelta.relativedelta(
                    months=1)).strftime("%Y%m")

            # 征缴计划 转DataFrame格式
            zjjh_df = pd.DataFrame(zjjh,
                                   columns=['征缴计划年月', '征缴计划金额', '实际缴费金额', '缴费到账时间', '单位缴费',
                                            '个人缴费'])
        else:
            # 处待校验完成后添加直接计算并新增列  基数 or 比例 存在非数字字符 则只增加列不计算  单位缴费 个人缴费 总金额
            data_check['单位缴费'] = ''
            data_check['个人缴费'] = ''
            data_check['总金额'] = ''
        # print(zjjh)

        # 将DataFrame写入数据库
        blerrdf = pd.DataFrame()
        if conn_flag == 0:
            data_check.to_sql('原始数据', conn, if_exists='replace', index=False)
            if isnumber_err == 0:
                conn.execute(
                    "UPDATE 原始数据 set 单位缴费= round(基数*单位比例/100,2),个人缴费=round(基数*个人比例/100,2)")
                conn.execute("UPDATE 原始数据 set 总金额=round(单位缴费+个人缴费,2)")
            # 同单位同月分缴费比例不同明细拉出核对，如果不校验，配置参数则可
            if self.check_blcy:
                # 将数据库写入DataFrame df   比例不一致年月对应明细信息
                blerrdf = pd.read_sql(
                    "SELECT * FROM v_征缴计划月明细 WHERE 征缴计划年月 IN ( SELECT 征缴计划年月 FROM 征缴计划月明细 GROUP BY 征缴计划年月 HAVING count( DISTINCT 比例 ) > 1 ) and 征缴计划年月 IN (select distinct 起始年月 from 原始数据) ORDER BY 征缴计划年月,序号",
                    conn)

        blerrs = blerrdf.shape[0]  # 判断缴费比例不一致数据记录数 或者 获取错误为0
        blerrstext = ''
        # print(blerrs, blerrdf)
        if blerrs > 0:
            blerrstext = "存在缴费比例不一致信息,请核对"
        # 异常信息记录数据存到errs
        errs = data_check[data_check['异常信息'] != '']
        well = '0'

        if errs.shape[0] == 0:
            well = '1'
        # print(well, conn_flag, blerrs)
        res_text = ''
        # 先导出错误信息
        if (well != '1' or blerrs != 0) and conn_flag == 0 and self.count:
            print(f"异常信息导出中…………")
            with pd.ExcelWriter(f'{self.export_dir}\{errs_filename}', engine='openpyxl') as writer:
                errs.to_excel(writer, index=False, sheet_name='异常信息',
                              header=['姓名', '公民身份号码', '起始年月', '截止年月', '基数', '单位比例', '个人比例',
                                      '清算类型', '户名', '银行账号', '开户行名称', '异常信息', '序号', '单位缴费',
                                      '个人缴费', '总金额'])
                data_check.to_excel(writer, index=False, sheet_name='原始数据',
                                    header=['姓名', '公民身份号码', '起始年月', '截止年月', '基数', '单位比例',
                                            '个人比例',
                                            '清算类型',
                                            '户名', '银行账号', '开户行名称', '异常信息', '序号', '单位缴费',
                                            '个人缴费',
                                            '总金额'])
                ws = writer.sheets['异常信息']
                worksheet1 = ws
                worksheet1.column_dimensions["B"].width = 20
                worksheet1.column_dimensions["H"].width = 16
                worksheet1.column_dimensions["J"].width = 21
                worksheet1.column_dimensions["K"].width = 13
                worksheet1.column_dimensions["L"].width = 30
                worksheet1.column_dimensions["M"].width = 6
                for a in range(1, len(errs) + 2):
                    cell = ws[f'L{a}']  # 例如，设置A1单元格的格式
                    cell.font = styles.Font(name='Calibri', size=11, bold=True, color='000000')  # 设置字体和加粗
                    cell.fill = styles.PatternFill(start_color='FFFF00', end_color='FFFF00',
                                                   fill_type='solid')  # 设置背景色为黄色
                    cell.border = styles.Border(left=styles.Side(style='thin'), right=styles.Side(style='thin'),
                                                top=styles.Side(style='thin'),
                                                bottom=styles.Side(style='thin'))  # 设置边框
                ws2 = writer.sheets['原始数据']
                worksheet2 = ws2
                worksheet2.column_dimensions["B"].width = 20
                worksheet2.column_dimensions["H"].width = 16
                worksheet2.column_dimensions["J"].width = 21
                worksheet2.column_dimensions["K"].width = 13
                worksheet2.column_dimensions["L"].width = 30
                worksheet2.column_dimensions["M"].width = 6
                # 设置特定单元格的格式
                for a in range(2, len(data_check) + 2):
                    cell = ws2[f'L{a}']  # 例如，设置A1单元格的格式
                    if cell.value is not None:
                        cell.font = styles.Font(name='Calibri', size=11, bold=True, color='000000')  # 设置字体和加粗
                        cell.fill = styles.PatternFill(start_color='FFFF00', end_color='FFFF00',
                                                       fill_type='solid')  # 设置背景色为黄色
                        cell.border = styles.Border(left=styles.Side(style='thin'), right=styles.Side(style='thin'),
                                                    top=styles.Side(style='thin'),
                                                    bottom=styles.Side(style='thin'))  # 设置边框

                if blerrs != 0 and self.check_blcy:
                    print(
                        f"【注意】存在比例信息同征缴年月不一致情况，请仔细确认，具体参考异常信息【比例不一致月份征缴明细】页面数据")
                    blerrdf.to_excel(writer, index=False, sheet_name='比例不一致月份征缴明细',
                                     header=['征缴计划年月', '姓名', '公民身份号码', '起始年月', '截止年月', '基数',
                                             '单位比例',
                                             '个人比例', '清算类型', '户名', '银行账号', '开户行名称', '异常信息',
                                             '序号',
                                             '单位缴费', '个人缴费', '总金额'])
            res_text = f"异常信息已导出，共{len(errs)}条记录。" + blerrstext
        else:
            if errs.shape[0] == 0 and not blerrstext:
                res_text = f"已校验完成，无异常信息。" + blerrstext
            else:
                res_text = f"已校验完成，共{len(errs)}条异常信息。" + blerrstext

        # 再判断 导出征缴信息
        if well == '1' and conn_flag == 0 and self.count:
            print(f"征缴计划生成中…………")
            # 征缴计划年月	征缴计划金额	实际缴费金额	缴费到账时间 export_dir export_filename
            with pd.ExcelWriter(f'{self.export_dir}/{export_filename}', engine='openpyxl') as wr_zjjh:
                zjjh_df.to_excel(wr_zjjh, index=False, sheet_name='原试点符合新制度单位到账信息采集表',
                                 header=['征缴计划年月', '征缴计划金额', '实际缴费金额', '缴费到账时间', '单位缴费',
                                         '个人缴费'])
                worksheet1 = wr_zjjh.sheets['原试点符合新制度单位到账信息采集表']
                worksheet1.column_dimensions["A"].width = 14
                worksheet1.column_dimensions["B"].width = 14
                worksheet1.column_dimensions["C"].width = 14
                worksheet1.column_dimensions["D"].width = 14
                worksheet1.column_dimensions["E"].width = 8
                worksheet1.column_dimensions["F"].width = 8
                cell = worksheet1[f'A{len(zjjh_df) + 2}']  # 例如，设置A1单元格的格式
                cell.value = '合计:'
                cell.fill = styles.PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')  # 设置背景色为黄色
                cellS = worksheet1[f'B{len(zjjh_df) + 2}']  # 例如，设置A1单元格的格式
                cellS.value = F"=SUM(B2:B{len(zjjh_df) + 1})"
                cellS.fill = styles.PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')  # 设置背景色为黄色
                thin_border = Border(
                    left=Side(style='thin'),
                    right=Side(style='thin'),
                    top=Side(style='thin'),
                    bottom=Side(style='thin')
                )
                for row in worksheet1.iter_rows():
                    for cell in row:
                        if cell.column == 1:
                            cell.alignment = styles.Alignment(horizontal='center', vertical='center')
                        else:
                            cell.alignment = styles.Alignment(horizontal='right', vertical='center')

                        cell.border = thin_border

            print(f"征缴计划{begin_month}-{end_month}时间段已生成完成。请查看【{self.export_dir}/{export_filename}】文件")
            res_text = f"征缴计划{begin_month}-{end_month}时间段已生成.\n" + res_text
        else:
            res_text = '' + res_text

        print(res_text)
        if conn_flag == 0:
            conn.close()
        return res_text, errs


if __name__ == '__main__':
    t = checkdata()
    t.count = True
    filename = '模拟  第二步2025.6.12错误.xlsx'
    t,f = t.check_date(filename)
    print(t,f)